//    Copyright (C) Kherty.  All rights reserved.
using System;
using System.Collections;
using System.Collections.Generic;
using System.ComponentModel;
using System.Diagnostics;
using System.Diagnostics.Contracts;
using System.Globalization;
using System.IO;
using System.Windows;
using System.Windows.Documents;
using System.Windows.Media;
using OpenLS.Spreadsheet.Expressions;
using OpenLS.Core;
using OpenLS.Spreadsheet.Extensibility;
using OpenLS.Spreadsheet.Internals;
using OpenLS.Spreadsheet.Parsing;

namespace OpenLS.Spreadsheet
{
#if DEBUG
    [ContractClassFor(typeof (Range))]
    abstract class RangeContract: Range
    {
        internal override object GetCellValue(CellProperty p, out bool uniform)
        {
            Contract.Ensures(!(Contract.Result<object>() is Exception));
            throw new NotSupportedException();
        }
        internal override RangeAddress Address
        {
            get
            {
                Contract.Ensures(!(this is FullRowRange) || Contract.Result<RangeAddress>().IsFullRow);
                Contract.Ensures(!(this is FullColumnRange) || Contract.Result<RangeAddress>().IsFullColumn);
                throw new NotSupportedException(
                    );
            }
        }
    }
    
#endif
    ///<summary>
    /// Represents a set of cells in a worksheet.
    ///</summary>
#if DEBUG
    [ContractClass(typeof (RangeContract))]
#endif
    public  abstract partial class Range: INotifyPropertyChanged, IArray, ICellEnumerator
    {
        public override sealed string ToString()
        {
            return Label;
        }
        internal abstract IEnumerable<SimpleRange> GetSimpleRanges();

        internal void SetColumnsWidth(double w)
        {
            using (Worksheet.CreateUndoScope(StringTable.Width))
            {
                SetColumnsWidthNoUndo(w);
            }
        }
        /// <summary>
        /// Clear all borders from the range
        /// </summary>
        public void ClearBorders()
        {
            using (Worksheet.Workbook.CreateUndoScope(StringTable.Clear + " " + StringTable.Borders))
            {
                foreach (BorderType borderType in Enum.GetValues(typeof (BorderType)))
                {
                    SetBorder(null, borderType);
                }
            }
        }
        /// <summary>
        /// Set the border for a given range edge
        /// </summary>
        /// <param name="border">The value of the border to set</param>
        /// <param name="borderType">The type of border to set</param>
        public void SetBorder(Border border, BorderType borderType)
        {
            using (var u = this.Worksheet.CreateUndoScope(StringTable.Borders))
            {
                SetBorderNoUndo(border, borderType);
            }
        }
        internal abstract void SetBorderNoUndo(Border border, BorderType borderType);
        ///<summary>
        /// Set the array formula of the range.
        ///</summary>
        ///<param name="text">The text of the array formula</param>
        ///<param name="localized">Specifies whether the text is localized</param>
        public void SetArrayFormula (string text, bool localized)
        {
            using (Worksheet.Workbook.CreateUndoScope(StringTable.Formula))
            {
                SetArrayFormulaImp(text, localized?LocalizationMode.Localized:LocalizationMode.NonLocalized);
            }
        }
        ///<summary>
        /// Set the array formula of the range.
        ///</summary>
        ///<param name="text">The text of the array formula. The text of the formula should not be localized</param>
        public void SetArrayFormula(string text)
        {
            using (Worksheet.Workbook.CreateUndoScope(StringTable.Formula))
            {
                SetArrayFormulaImp(text, LocalizationMode.NonLocalized);
            }
        }

        private void SetArrayFormulaImp(string text, LocalizationMode localized)
        {
            var topLeft = TopLeftCell;
            topLeft.SetFormulaImp(text, localized, this);
            var expression = topLeft.Expression as ArrayFormulaReference;
            if (expression != null)
            {
                if (this.Surface != 1)
                {
                    foreach (var cell in this.GetAllCells())
                    {
                        if (cell == topLeft)
                            continue;
                        cell.Expression = new ArrayFormulaReference(expression.ArrayFormula);
                    }
                }
            }
        }

        internal event EventHandler<CellEventArgs> CellAdded
        {
            add
            {
                if (cellAddedHandlers == null)
                {
                    cellAddedHandlers = new List<EventHandler<CellEventArgs>>();
                    Worksheet.AddWeakCellAdded((sender, args) =>
                                                {
                                                    if (!Contains(args.Cell))
                                                        return;
                                                    foreach (var handler in cellAddedHandlers)
                                                    {
                                                        handler(sender, args);
                                                    }
                                                });
                }
                cellAddedHandlers.Add(value);
            }
            remove
            {
                cellAddedHandlers.Remove(value);
            }
        }
        List<EventHandler<CellEventArgs>> cellAddedHandlers ;

        internal virtual void SetColumnsWidthNoUndo(double w)
        {
            foreach (Column column in GetColumns())
                column.Width = w;
        }

        internal void SetRowsHeight(double value)
        {
            using (Worksheet.CreateUndoScope(StringTable.Height))
            {
                SetRowsHeightNoUndo(value);
            }
        }

        internal virtual void SetRowsHeightNoUndo(double value)
        {
            foreach (Row row in GetRows())
                row.Height = value;
        }

        internal void AutoSum (string functionName)
        {
            NavigationDirection ? orientation = NavigationDirection.Up;
            if (ColumnSpan < RowSpan)
                orientation = NavigationDirection.Left;
            if (TopLeftCell.Address .Equals( new CellAddress()))
                orientation = NavigationDirection.Right;
            if (Surface > 200)
                return; //\\too big
            foreach (Cell cell in GetAllCells())
            {
                Range r;
                if (cell.Address.Column == 0 && cell.Address.Row == 0)
                    r = new SingleCellRange(cell.GetNeighbor(NavigationDirection.Right));
                else
                {
                    r = FindRangeFor(cell, orientation);
                }
                if (r != null)
                    cell.Formula = "=" + functionName + "(" + r.Label + ")";
            }

        }
        internal event EventHandler CellValueChanged  //\\ OK?
        {
            add
            {
                Worksheet.CellValueChanged += delegate(object sender, CellValueChangedEventArgs<object> args)
                                                  {
                                                      var cell = (Cell)sender;
                                                      if (Contains(cell))
                                                          try
                                                          {
                                                              value(cell, args);
                                                          }
                                                          catch (Exception)
                                                          { }
                                                  };
            }

            remove
            {
                Worksheet.CellValueChanged -= delegate(object sender, CellValueChangedEventArgs<object> args)
                                                  {
                                                      var cell = (Cell)sender;
                                                      if (Contains(cell))
                                                          try
                                                          {
                                                              value(cell, args);
                                                          }
                                                          catch (Exception)
                                                          { }
                                                  };
            }

        }

        ///<summary>
        /// Get the cell in the top left corner of the range
        ///</summary>
        public abstract Cell TopLeftCell { get;}
        internal abstract void CopyFromImp(CellAddress sourceTopLeft, IEnumerable<SimpleRange> source, PasteInfo pasteInfo);
        internal void CopyFrom(Range source, PasteInfo info)
        {
            Contract.Requires(source != null);
            Contract.Requires(info != null);
            CopyFromImp(source.TopLeftCell.Address, source.GetSimpleRanges(), info);
            //\\ if (source.Worksheet == Worksheet)
                info.Functions.TableFunction.ClearFunction(source);
        }
        internal abstract  bool IsSingleCell { get;}
        public virtual bool Equals(Range r)
        {
            if (Worksheet != r.Worksheet)
                return false;
            if (r is CompositeRange)
            {
                CompositeRange cr = (CompositeRange) r;
                return r.Equals(this);
            }
            return Address.Equals(r.Address);
        }
        public string NameOrLabel
        {
            get
            {
                foreach (DefinedName n in Worksheet.Workbook.DefinedNames)
                    if (n.Matches(this))
                        return n.Name;
                if (this is SingleCellRange)
                    return ((SingleCellRange) this).Cell.Label;
                return Label;
            }
        }
        internal abstract Range GetColumnRange(int index, RangeType type);
        internal abstract Range GetRowRange(int index, RangeType type);
        public abstract Cell this[int row, int column] { get;}
        static Range FindRangeFor(Cell cell, NavigationDirection? dir)
        {
            if (dir != null)
                return FindRangeForInDirection(cell, dir.Value);
            Range upRange = FindRangeForInDirection(cell, NavigationDirection.Up);
            Range leftRange = FindRangeForInDirection(cell, NavigationDirection.Left);
            if (upRange == null)
            {
                if (leftRange != null)
                    return leftRange;
                return null;
            }
            if (leftRange == null)
                return upRange;
            if (upRange.Surface >= leftRange.Surface)
                return upRange;
            return leftRange;
        }

        internal int Surface
        {
            get
            {
                return ColumnSpan * RowSpan;
            }
        }
        static Range FindRangeForInDirection(Cell cell, NavigationDirection direction)
        {
            Cell c = cell.GetNeighbor(direction);
            Cell lastGoodCell = null;
            int i = 0;
            while (c != null)
            {
                if (c.Value != null)
                {
                    lastGoodCell = c;
                }
                else if (lastGoodCell != null)
                    break;
                Cell newCell = c.GetNeighbor(direction);
                if (newCell == null || c == newCell)
                    break;
                c = newCell;
                if (i++ > 100)
                    break;
            }
            if (lastGoodCell != null)
                return CellsRange.Create(c.Worksheet, new RangeAddress(lastGoodCell.Address, cell.GetNeighbor(direction).Address));
            return null;
        }

        internal abstract RangeAddress Address { get;}
        internal abstract Cell FirstCell { get;}
        public virtual int Row { get { return FirstCell.Row.Index; } }
        public virtual int Column { get { return FirstCell.Column.Index; } }
        public abstract int RowSpan { get;}
        public abstract int ColumnSpan { get;}
        public abstract bool IsFullRow{ get;}
        public abstract bool IsFullColumn { get;}
        private T GetRefCellValue<T>(CellProperty p) where T : class
        {
            bool uniform;
            object result = GetCellValue(p, out uniform);
            if (uniform)
                return (T)result;
            return null;
        }
        private T? GetValueCellValue<T>(CellProperty p) where T : struct
        {
            bool uniform;
            object result = GetCellValue(p, out uniform);
            if (uniform && result != null)
                return (T)result;
            return null;
        }
        public FontWeight? FontWeight
        {
            get
            {
                return GetValueCellValue<FontWeight>(CellProperty.FontWeight);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.FontWeight, value);
            }
        }
        /// <summary>
        /// Get or set the foregroung color of the range.
        /// </summary>
        public SpreadsheetColor Foreground
        {
            get
            {
                return GetRefCellValue<SpreadsheetColor>(CellProperty.Foreground);
            }
            set
            {
                SetCellValue(CellProperty.Foreground, value);
            }
        }
        ///<summary>
        /// Get or set the background of the range.
        ///</summary>
        public Fill Background
        {
            get
            {
                return GetRefCellValue<Fill>(CellProperty.Background);
            }
            set
            {
                SetCellValue(CellProperty.Background, value);
            }
        }
        ///<summary>
        /// Get or set the text wrapping of cells in this range.
        ///</summary>
        public TextWrapping? TextWrapping
        {
            get
            {
                return GetValueCellValue<TextWrapping>(CellProperty.TextWrapping);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.TextWrapping, value);
            }
        }
        ///<summary>
        /// Get or set the vertical of cells in this range.
        ///</summary>
        public VerticalCellAlignment? VerticalAlignment
        {
            get
            {
                return GetValueCellValue<VerticalCellAlignment>(CellProperty.VerticalAlignment);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.VerticalAlignment, value);
            }
        }
        public HorizontalCellAlignment? HorizontalAlignment
        {
            get
            {
                return GetValueCellValue<HorizontalCellAlignment>(CellProperty.HorizontalAlignment);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.HorizontalAlignment, value);
            }
        }
        public FontStyle? FontStyle
        {
            get
            {
                return GetValueCellValue<FontStyle>(CellProperty.FontStyle);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.FontStyle, value);
            }
        }
        ///<summary>
        /// Get or set the format for this range.
        ///</summary>
        public Format Format
        {
            get
            {
                return GetRefCellValue<Format>(CellProperty.Format);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.Format, value);
            }
        }
        public UnderlineType? FontUnderline
        {
            get
            {
                return GetValueCellValue<UnderlineType>(CellProperty.FontUnderline);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.FontUnderline, value);
            }
        }
        public FontFamily FontFamily
        {
            get
            {
                return GetRefCellValue<FontFamily>(CellProperty.FontFamily);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.FontFamily, value);
            }
        }
        public double? FontSize
        {
            get
            {
                return GetValueCellValue<double>(CellProperty.FontSize);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.FontSize, value.Value);
            }
        }

        ///<summary>
        /// Set or get whether the cell is locked and cannot be edited. This value of this property is meaningful only if the worksheet is protected.
        ///</summary>
        public bool? IsLocked
        {
            get
            {
                return GetValueCellValue<bool>(CellProperty.IsLocked);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.IsLocked, value.Value);
            }
        }
        internal bool? IsHidden
        {
            get
            {
                return GetValueCellValue<bool>(CellProperty.IsHidden);
            }
            set
            {
                if (value == null)
                    return;
                SetCellValue(CellProperty.IsHidden, value.Value);
            }
        }

        internal void ToggleCellValue(CellProperty p)
        {
            object val = GetFirstCellValue(p);
            object toggledValue;
            switch (p)
            {
                case CellProperty.FontUnderline:
                    var underlineType = (UnderlineType)val;
                    toggledValue = underlineType == UnderlineType.None ? UnderlineType.Single : UnderlineType.None;
                    break;
                case CellProperty.TextWrapping:
                    var textWrapping = (TextWrapping)val;
                    toggledValue = textWrapping == System.Windows.TextWrapping.NoWrap ? System.Windows.TextWrapping.Wrap : System.Windows.TextWrapping.NoWrap;
                    break;
                case CellProperty.FontWeight:
                    var fontWeight = (FontWeight)val;
                    toggledValue = fontWeight == FontWeights.Normal ? FontWeights.Bold : FontWeights.Normal;
                    break;
                case CellProperty.FontStyle:
                    var fontStyle = (FontStyle)val;
                    toggledValue = fontStyle == FontStyles.Normal ? FontStyles.Italic : FontStyles.Normal;
                    break;
                    case CellProperty.IsLocked:
                    bool locked =(bool) val;
                    toggledValue = !locked;
                    break;
                    
                default:
                    throw new NotSupportedException();
            }
            SetCellValue(p, toggledValue);
        }
        internal virtual void SetCellValue(CellProperty property, object value)
        {
            using (Worksheet.CreateRangeUndoScope(this, property))
            {
                SetCellValueNoUndo(property, value);
                NotifyPropertyChanged(property);
            }
        }
        internal abstract void SetCellValueNoUndo(CellProperty p, object value);
        ///<summary>
        /// Get the worksheet which contains this range.
        ///</summary>
        public abstract Worksheet Worksheet { get;}
        IWorksheet IRange.Worksheet{get{ return this.Worksheet;}}
        public abstract string Label{get;}
        internal virtual string LabelForSelection { get { return Label; } }
        internal virtual string ShortLabel { get { return Label; } }
        public string FullLabel
        {
            get
            {
                
                return Worksheet.SafeName + "!" + Label;
            }
        }
        internal string FullShortLabel { get { return Worksheet.Name + "!" + ShortLabel; } }
        internal string ShortLabelRelativeTo(Worksheet worksheet)
        {
            if (worksheet == Worksheet)
                return ShortLabel;
            return FullShortLabel;
        }

        internal abstract object GetFirstCellValue(CellProperty p);
        internal abstract CellFormattingRecord GetFormattingRecord();
        internal abstract IEnumerable<CellFormattingRecord> GetFormattingRecords();

        internal static bool Intersects(Range range1, Range range2)
        {
            if (range1 == range2)
                return true;
            if (range1.Worksheet != range2.Worksheet)
                return false;
            if (range1 is FullWorksheetRange)
                return true;
            if (range2 is FullWorksheetRange)
                return true;
            if (range1 is FullRowRange)
            {
                if (range2 is FullRowRange)
                    return ((FullRowRange)range1).Intersects(((FullRowRange)range2));
                if (range2 is FullColumnRange)
                    return true;
                if (range2 is CellsRange)
                    return (range2.Address.Intersects(((FullRowRange)range1).FirstRow));
            }
            if (range1 is FullColumnRange)
            {
                if (range2 is FullColumnRange)
                    return ((FullColumnRange)range1).Intersects(((FullColumnRange)range2));
                if (range2 is FullRowRange)
                    return true;
                if (range2 is CellsRange)
                    return (range2.Address.Intersects(((FullColumnRange)range1).FirstColumn)); //\\OK?
            }
            if (range1 is CellsRange)
            {
                if (range2 is FullColumnRange)
                    return (range1.Address.Intersects(((FullColumnRange)range2).FirstColumn));
                if (range2 is FullRowRange)
                    return (range1.Address.Intersects(((FullRowRange)range2).FirstRow));
                if (range2 is CellsRange)
                    return (((CellsRange)range1).Intersects(((CellsRange)range2)));

            }
            if (range1 is CompositeRange)
            {
                var compositeRange = (CompositeRange)range1;
                foreach (Range r in compositeRange.Ranges)
                {
                    if (Intersects(r, range2))
                        return true;
                }
                return false;
            }
            return Intersects(range2, range1); //\\ careful with infinite loops...
        }

        internal abstract object GetCellValue(CellProperty p, out bool uniform);

        internal abstract IEnumerable<Cell> GetNonEmptyCells();
        //Get all existing cells, volatile or not
        internal IEnumerable<Cell> GetExistingCells()
        {
            return Enumerable.Union(GetNonEmptyCells(), GetVolatileCells());
        }
        private IEnumerable<Cell> GetVolatileCells()
        {
            if (Surface > this.Worksheet.Cells.VolatileCellCount)
            {
                foreach (var cell in this.Worksheet.Cells.GetVolatileCells())
                {
                    if (this.Contains(cell))
                        yield return cell;
                }
            }
            else
            {
                foreach (var cell in Enumerable.Where( Enumerable.Select(Address.GetCellAddresses(), a => Worksheet.Cells.GetVolatileCellIfAny(a)), c => c != null))
                {
                    yield return cell;
                }
            }
        }


        internal void Filter(Predicate<PartialRowOrColumnRange> predicate)
        {
            using (Worksheet.CreateUndoScope(StringTable.Filter))
            {
                var toFilter = new List<PartialRowOrColumnRange>(GetPartialRowOrColumnRanges(RowOrColumn.Row));
                foreach (PartialRowOrColumnRange r in toFilter)
                {
                    r.Cells[0].Row.IsHidden = !predicate(r);
                }
            }
            
        }

        internal abstract IEnumerable<PartialRowOrColumnRange> GetPartialRowOrColumnRanges(RowOrColumn c);
        internal abstract IEnumerable<Range> GetRowRanges(RangeType type);

        internal abstract IEnumerable<Range> GetColumnRanges(RangeType type);

        internal abstract bool Contains(CellAddress address);

        internal bool Contains(Cell cell)
        {
            if (cell.Worksheet != Worksheet)
                return false;
            return Contains(cell.Address);
        }

        internal void ClearValues()
        {
            using (Worksheet.CreateUndoScope(StringTable.Clear))
            {
                foreach (Cell c in GetNonEmptyCells())
                {
                    c.ClearValue();
                }
            }
        }

        internal void ClearCellValue(CellProperty cellProperty)
        {
            using (Worksheet.CreateUndoScope(StringTable.Clear))
            {
                foreach (Cell c in GetNonEmptyCells())
                    c.ClearCellValue(cellProperty);//\\implement for row/columns
            }
        }

        internal abstract void SetStyleNoUndo(CellStyle s);
        ///<summary>
        /// Set the style of the range. This method extracts the properties of the style and applies them to all cells and dimensions in the current range.
        ///</summary>
        ///<param name="style">The style to apply</param>
        ///<exception cref="ArgumentNullException"></exception>
        public void SetStyle(CellStyle s)
        {
            using (Worksheet.CreateUndoScope(StringTable.Style))
            {
                SetStyleNoUndo(s);
            }
        }

        public CellStyle Style
        {
            get
            {
                var cellRecords = Enumerable.Where(Enumerable.Select(Enumerable.Where(this.GetFormattingRecords(), r => r != null), r => r.Parent), r => r != null);
                CellStyle result = null;
                foreach (var record in cellRecords)
                {
                    if (result == null)
                        result = record.Style;
                    else if (result != record.Style)
                        return null;
                }
                return result;
            }
            set
            {
                Contract.Ensures(Style == value);
                using (Worksheet.CreateUndoScope(StringTable.Style))
                {
                    SetStyleNoUndo(value);
                }

            }
        }
        
        

        [Obsolete]
        internal static Range FromAddress(Worksheet ws, RangeAddress address)
        {
            if (address.IsFullRow)
            {
                return new FullRowRange(ws.Rows[address.TopLeft.Row], ws.Rows[address.BottomRight.Row]);
            }
            if (address.IsFullColumn)
            {
                return new FullColumnRange(ws.Columns[address.TopLeft.Column], ws.Columns[address.BottomRight.Column]);
            }
            return new MultiCellsRange(ws, address);
        }

        #region INotifyPropertyChanged Members

        public event PropertyChangedEventHandler PropertyChanged; //\\ internal?

        internal void NotifyPropertyChanged(CellProperty p)
        {
           
           if (PropertyChanged != null && !Worksheet.Workbook.InPreviewMode)
                PropertyChanged(this, new PropertyChangedEventArgs(p.ToString()));
        }
        internal void NotifyNameOrLabelChanged()
        {
            if (PropertyChanged != null)
                PropertyChanged(this, new PropertyChangedEventArgs("NameOrLabel"));
        }

        #endregion

        internal abstract Cell GetNextCell(Cell cell, CellNavigationDirection direction);
       // internal abstract Cell GetNextCell(Cell cell);
       // internal abstract Cell GetPreviousCell(Cell cell);


        internal abstract RangeReference ToReference();


        internal void SetStyleRecord(CellFormattingRecord styleRecord)
        {
            using (Worksheet.Workbook.CreateUndoScope(StringTable.FormatCells))
            {
                SetStyleRecordNoUndo(styleRecord);
            }
        }

        internal virtual void SetStyleRecordNoUndo(CellFormattingRecord styleRecord)
        {
            foreach (Cell cell in GetNonEmptyCells())
                cell.SetFormattingRecordUnsafe(styleRecord);
        }

        internal abstract FlowDocument ToFlowDocument(string columnSeparator);

        /// <summary>
        /// Enumerates all the cells in the current range
        /// </summary>
        /// <returns>The cells</returns>
        public abstract IEnumerable<Cell> GetAllCells();

        internal abstract Range Offseted(CellRelativeAddress address);
        internal Range Projected (Worksheet worksheet, CellRelativeAddress range)
        {
            Contract.Requires(worksheet != null);
            Contract.Ensures(Contract.Result<Range>() != null);
            Contract.Ensures(Contract.Result<Range>().Worksheet == worksheet);
            if (this is SimpleRange)
                return ((SimpleRange) this).ProjectedSimple(worksheet, range);
            else
            {
                CompositeRange cr = (CompositeRange) this;
                return new CompositeRange(Enumerable.Select(cr.Ranges, r => r.ProjectedSimple(worksheet, range)));
            }
        }

        public abstract IEnumerable<Column> GetColumns();
        internal virtual IEnumerable<Column> GetNonEmptyColumns() { return GetColumns(); }

        public abstract IEnumerable<Row> GetRows();
        internal virtual IEnumerable<Row> GetNonEmptyRows() { return GetRows(); }

        /// <summary>
        /// Clear all formats from the range
        /// </summary>
        public void ClearFormats()
        {
            ClearInternal(StringTable.ClearFormats, RangeFunctions.ApplyFormats.ClearCell);
        }
        /// <summary>
        /// Clear all content and formatting from the range.
        /// </summary>
        public void Clear()
        {
            ClearInternal(StringTable.Clear, RangeFunctions.ApplyAll.ClearCell);
        }
        /// <summary>
        /// Clear the values and formula from the range
        /// </summary>
        public void ClearContents()
        {
            ClearInternal(StringTable.ClearContents, RangeFunctions.ApplyValueAndFormula.ClearCell);
            //            worksheet.Selection.Clear(SpreadsheetEditingCommands.ClearContents.Text,
  //                                    RangeFunctions.ApplyValueAndFormula.ClearCell);

        }
        /// <summary>
        /// Clear all the comments from the range
        /// </summary>
        public void ClearComments()
        {
            ClearInternal(StringTable.ClearComments, c => c.Comment = null);

    }
        /// <summary>
        /// Clear all hyperlinks from the range.
        /// </summary>
        public void ClearHyperlinks()
        {
            ClearInternal(StringTable.ClearHyperlinks, c => c.Hyperlink = null);
        }

        internal void ClearInternal(string name, Action<Cell> cellUnaryFunction)
        {
            using (Worksheet.Workbook.CreateUndoScope(name))
            {
                foreach (Cell cell in GetNonEmptyCells())
                    cellUnaryFunction(cell);
            }
        }

        internal FullRowRange ToFullRowRange()
        {
            return new FullRowRange(TopLeftCell.Row, Worksheet.Rows[TopLeftCell.Row.Index + (RowSpan - 1)]);
        }

        internal FullColumnRange ToFullColumnRange()
        {
            return new FullColumnRange(TopLeftCell.Column, Worksheet.Columns[TopLeftCell.Column.Index + (ColumnSpan - 1)]);
        }

        #region IArray Members

        object IArray.this[int row, int column]
        {
            get
            {
                if (row < 0 || column < 0)
                    throw new ErrorException(Errors.NaError);
                if (row + Row > Worksheet.MaxRowIndex)
                    throw new ErrorException(Errors.NaError);
                if (column + Column > Worksheet.MaxColumnIndex)
                    throw new ErrorException(Errors.NaError);
                return this[row, column].Value;
            }
        }

        int IArray.RowCount
        {
            get { return RowSpan; }
        }

        int IArray.ColumnCount
        {
            get { return ColumnSpan; }
        }

        internal abstract bool IsProtected { get; }

        IEnumerator<object> IEnumerable<object>.GetEnumerator()
        {
            foreach (Cell cell in GetNonEmptyCells())
            {
                if (cell.HasSpan && !Contains(cell.Address))
                    continue;
                yield return cell;
            }
        }

        #endregion

        internal virtual int GetCellIndex(Cell c)
        {
            return c.Row.Index + c.Column.Index - TopLeftCell.Row.Index - TopLeftCell.Column.Index;
        }


        //internal abstract Range Projected(Worksheet worksheet, CellRelativeAddress diff);



        internal bool ContainsColumn(Column column)
        {
            Contract.Requires(column != null);
            int columnIndex = column.Index;
            return columnIndex >= Column && columnIndex < Column + ColumnSpan;
        }
        internal bool ContainsRow(Row row)
        {
            Contract.Requires(row != null);
            int rowIndex = row.Index;
            return rowIndex >= Row && rowIndex < Row + RowSpan;
        }

        internal static Range Create(Worksheet worksheet, RangeAddress rangeAddress, bool adjust)
        {
            if (rangeAddress.IsFullColumn)
            {
                if (!rangeAddress.IsFullRow)
                {
                    return new FullColumnRange(worksheet.Columns[rangeAddress.TopLeft.Column],
                                               worksheet.Columns[rangeAddress.BottomRight.Column]);
                }
                return new FullWorksheetRange(worksheet);
            }
            if (rangeAddress.IsFullRow)
                return new FullRowRange(worksheet.Rows[rangeAddress.TopLeft.Row],
                                        worksheet.Rows[rangeAddress.BottomRight.Row]);
            return CellsRange.Create(worksheet, rangeAddress, adjust);
        }


        internal abstract bool Contains(Range range);
        internal bool ContainsCompositeRange (Range range)
        {
            if (range is CompositeRange)
            {
                foreach (var r in ((CompositeRange)range).Ranges)
                {
                    if (!Contains(r))
                        return false;
                }
                return true;
            }
            return false;
        }

        IEnumerator IEnumerable.GetEnumerator()
        {
            IEnumerable<object> oo = this;
            return oo.GetEnumerator();
        }

        internal abstract Border GetBorder(BorderType borderType, out bool uniform);

        internal static Range Union(Range range1, Range range2)
        {
            Contract.Requires(range1 != null);
            Contract.Requires(range2 != null);
            Contract.Ensures(Contract.Result<Range>() != null);
            Contract.Ensures(Contract.Result<Range>().Contains(range1));
            Contract.Ensures(Contract.Result<Range>().Contains(range2));
            if (range1.Contains(range2))
                return range1;
            if (range2.Contains(range1))
                return range2;
            return new CompositeRange(Enumerable.Union(GetRanges(range1), GetRanges(range2)));
        }
        static IEnumerable<SimpleRange> GetRanges (Range range)
        {
            if (range is CompositeRange)
            {
                CompositeRange cr = (CompositeRange) range;
                foreach (var r in cr.Ranges)
                    yield return r;
            }
            else
            {
                yield return (SimpleRange) range;
            }
        }

        public static Range Intersection(Range range1, Range range2)
        {
            if (range1 == range2)
                return range1;
            if (range1.Worksheet != range2.Worksheet)
                return null;
            if (range1 is FullWorksheetRange)
                return range2;
            if (range2 is FullWorksheetRange)
                return range1;
            if (range1 is FullRowRange)
            {
                FullRowRange fullRowRange1 = (FullRowRange)range1;
                if (range2 is FullRowRange)
                {
                    FullRowRange fullRowRange2 = (FullRowRange)range2;
                    int minRow1 = Math.Max(fullRowRange1.FirstRow.Index, fullRowRange2.FirstRow.Index);
                    int maxRow = Math.Min(fullRowRange1.LastRow.Index, fullRowRange2.LastRow.Index);
                    if (minRow1 > maxRow)
                        return null;
                    return new FullRowRange(range1.Worksheet.Rows[minRow1], range1.Worksheet.Rows[maxRow]);
                }
                if (range2 is FullColumnRange)
                {
                    FullColumnRange fullColumnRange2 = (FullColumnRange) range2;
                    return CellsRange.Create(range1.Worksheet, new RangeAddress(
                                                                   new CellAddress(fullRowRange1.FirstRow.Index,
                                                                                   fullColumnRange2.FirstColumn.Index),
                                                                   new CellAddress(fullRowRange1.LastRow.Index,
                                                                                   fullColumnRange2.LastColumn.Index)));
                }
                if (range2 is CellsRange)
                {
                    CellsRange cellsRange = (CellsRange) range2;
                    if (cellsRange.Row < fullRowRange1.FirstRow.Index)
                        return null;
                    if (cellsRange.Row + cellsRange.RowSpan - 1 > fullRowRange1.LastRow.Index)
                        return null;
                    return CellsRange.Create(range1.Worksheet,
                                             new RangeAddress(
                                                 new CellAddress(
                                                     Math.Max(cellsRange.Row, fullRowRange1.FirstRow.Index),
                                                     cellsRange.Column),
                                                 new CellAddress(
                                                     Math.Min(cellsRange.Row + cellsRange.RowSpan - 1,
                                                              fullRowRange1.LastRow.Index),
                                                     cellsRange.Column + cellsRange.ColumnSpan - 1)
                                                 ));
                }
            }
            if (range1 is FullColumnRange)
            {
                FullColumnRange fullColumnRange1 = (FullColumnRange) range1;
                if (range2 is FullColumnRange)
                {
                    FullColumnRange fullColumnRange2 = (FullColumnRange) range2;
                    int minColumn = Math.Max(fullColumnRange1.FirstColumn.Index, fullColumnRange2.FirstColumn.Index);
                    int maxColumn = Math.Min(fullColumnRange1.LastColumn.Index, fullColumnRange2.LastColumn.Index);
                    if (minColumn > maxColumn)
                        return null;
                    return new FullColumnRange(range1.Worksheet.Columns[minColumn], range1.Worksheet.Columns[maxColumn]);
                }
                if (range2 is FullRowRange)
                {
                    FullRowRange fullRowRange2 = (FullRowRange)range2;
                    return CellsRange.Create(range1.Worksheet, new RangeAddress(
                                                                   new CellAddress(fullRowRange2.FirstRow.Index,
                                                                                   fullColumnRange1.FirstColumn.Index),
                                                                   new CellAddress(fullRowRange2.LastRow.Index,
                                                                                   fullColumnRange1.LastColumn.Index)));
                }
                if (range2 is CellsRange)
                {
                    CellsRange cellsRange = (CellsRange)range2;
                    if (cellsRange.Column < fullColumnRange1.FirstColumn.Index)
                        return null;
                    if (cellsRange.Column + cellsRange.ColumnSpan - 1 > fullColumnRange1.LastColumn.Index)
                        return null;
                    return CellsRange.Create(range1.Worksheet,
                                             new RangeAddress(
                                                 new CellAddress(
                                                     cellsRange.Row,
                                                     Math.Max(cellsRange.Column, fullColumnRange1.FirstColumn.Index)
                                                     ),
                                                 new CellAddress(
                                                     cellsRange.Row + cellsRange.RowSpan - 1,
                                                     Math.Min(cellsRange.Column + cellsRange.ColumnSpan - 1,
                                                              fullColumnRange1.LastColumn.Index)
                                                              )
                                                 ));
                }
            }
            if (range1 is CellsRange)
            {
                if (range2 is CellsRange)
                {
                    CellsRange cellsRange1 = (CellsRange) range1;
                    CellsRange cellsRange2 = (CellsRange) range2;
                    int row1 = Math.Max(cellsRange1.Row, cellsRange2.Row);
                    int row2 = Math.Min(cellsRange1.Row + cellsRange1.RowSpan - 1, cellsRange2.Row + cellsRange2.RowSpan - 1);
                    int column1 = Math.Max(cellsRange1.Column, cellsRange2.Column);
                    int column2 = Math.Min(cellsRange1.Column + cellsRange1.ColumnSpan - 1, cellsRange2.Column + cellsRange2.ColumnSpan - 1);
                    if (row1 > row2 || column1 > column2)
                        return null;
                        return CellsRange.Create(range1.Worksheet,
                                                 new RangeAddress(new CellAddress(row1, column1),
                                                                  new CellAddress(row2, column2)));

                }
            }
            if (range1 is CompositeRange)
            {
                var compositeRange = (CompositeRange)range1;
                var ranges = Enumerable.ToList(
                    Enumerable.Where(
                    Enumerable.Select(compositeRange.Ranges, r => Intersection(r, range2)), r => r != null));
                var ranges2 = Enumerable.ToList(Enumerable.SelectMany(ranges, r => r.GetSimpleRanges()));
                if (ranges2.Count == 0)
                    return null;
                return new CompositeRange(ranges2);
            }
            return Intersection(range2, range1); //\\ careful with infinite loops...
        }

        internal virtual IEnumerable<Row> GetExistingRows()
        {
            return GetRows();
        }

        internal virtual IEnumerable<Column> GetExistingColumns()
        {
            return GetColumns();
        }

        #if DEBUG
        public object ToFlowDocumentTest()
        {
            return ToFlowDocument(",");
        }
#endif

        internal void IncreaseIndentation()
        {
            double indentation = ((double)this.GetFirstCellValue(CellProperty.Indentation)) + 1;
            this.SetCellValue(CellProperty.Indentation, indentation);
            this.SetCellValue(CellProperty.HorizontalAlignment, HorizontalCellAlignment.Left);
        }

        internal void DecreaseIndentation()
        {
            double indentation = Math.Max(
                ((double)this.GetFirstCellValue(CellProperty.Indentation)) - 1, 0.0);
            this.SetCellValue(CellProperty.Indentation, indentation);
            this.SetCellValue(CellProperty.HorizontalAlignment, HorizontalCellAlignment.Left);
        }


        private static Format GetNumberFormatForCell(Cell cell)
        {
            Format fmt = cell.Format;
            if (fmt is GeneralFormat)
            {
                var numberFormat = (NumberFormat)Core.Formats.Number;
                if (cell.Value is double)
                {
                    int count = availabledecimalCount(cell);
                    //\\ Math.Min(count, (int)cell.Column.ActualCharacterWidth);
                    for (int i = 0; i < count; i++)
                        numberFormat = (NumberFormat)numberFormat.IncreaseDecimal();
                }
                fmt = numberFormat;
            }
            return fmt;
        }
        private static int availabledecimalCount(Cell cell)
        {
            string str = cell.FormattedValue;
            int lastIndex = str.LastIndexOf(NumberFormatInfo.CurrentInfo.NumberDecimalSeparator);
            if (lastIndex == -1)
                return 0;
            return str.Length - lastIndex - 1;
        }


        internal void DecreaseDecimal()
        {
            using (Worksheet.Workbook.CreateUndoScope(StringTable.FormatCells))
            {
                foreach (Cell cell in this.GetNonEmptyCells())
                {
                    Format fmt = GetNumberFormatForCell(cell);
                    if (!(fmt is NumberFormat))
                        continue;
                    string code = fmt.Code;
                    if (code.Length <= 1)
                        continue;
                    cell.Format = ((NumberFormat)fmt).DecreaseDecimal();
                }
                if (this is FullRange)
                {
                    var fr = (FullRange)this;
                    foreach (var d in fr.GetDimensions())
                    {
                        var fmt = d.Format;
                        if (fmt is NumberFormat)
                        {
                            var n = (NumberFormat)fmt;
                            if (n.Code.Length > 1)
                            {
                                d.Format = n.DecreaseDecimal();
                            }
                        }
                    }
                }
            }
        }

        internal void IncreaseDecimal()
        {
            using (Worksheet.Workbook.CreateUndoScope(StringTable.FormatCells))
            {
                foreach (Cell cell in GetNonEmptyCells())
                {
                    //Format fmt = cell.Format;
                    Format fmt = GetNumberFormatForCell(cell);
                    if (!(fmt is NumberFormat))
                        continue;
                    cell.Format = ((NumberFormat)fmt).IncreaseDecimal();
                    if (cell.Column.ActualWidth < cell.DesiredWidth)
                        cell.Column.Width = cell.DesiredWidth;
                }
                if (this is FullRange)
                {
                    var fr = (FullRange)this;
                    foreach (var d in fr.GetDimensions())
                    {
                        var fmt = d.Format;
                        if (fmt is NumberFormat)
                        {
                            var n = (NumberFormat)fmt;
                            if (n.Code.Length > 1)
                            {
                                d.Format = n.IncreaseDecimal();
                            }
                        }
                    }
                }

            }
        }
    }
}
